<?php

class financeHandler {
	private $dbca;
	
	public function __construct( $dbca=null ) {
		$this->dbca = $dbca;
	}
	
	//获取对账订单信息
	public function searchBalanceBillInfo (&$importData=array()){
		$startDate = isset($importData['startDate']) ? trim($importData['startDate']) : '';
    	$endDate = isset($importData['endDate']) ? trim($importData['endDate']) : '';
    	$page = isset($importData['page']) ? trim($importData['page']) : '';//当前页
    	$rp = isset($importData['rp']) ? trim($importData['rp']) : '';//每页记录数
    	$sortname = isset($importData['sortname']) ? trim($importData['sortname']) : '';//排序字段名
    	$sortorder = isset($importData['sortorder']) ? trim($importData['sortorder']) : '';//排序方式
    	$bill_no = isset($importData['bill_no']) ? trim($importData['bill_no']) : '';
    	$package_no = isset($importData['package_no']) ? trim($importData['package_no']) : '';
    	$deliver_name = isset($importData['deliver_name']) ? trim($importData['deliver_name']) : '';
    	$deliver_mobile = isset($importData['deliver_mobile']) ? trim($importData['deliver_mobile']) : '';
    	$payTypeID = isset($importData['payTypeID']) ? trim($importData['payTypeID']) : '';
    
    	$sql_count = "SELECT COUNT(*) countNum FROM bill_master bm";
    
    	$sql_search = "SELECT bm.*, bp.deduct_amount FROM bill_master bm INNER JOIN bill_pay bp ON bm.bill_no = bp.bill_no";
    	
    	//排序
    	$str_order = '';
    
    	//分页
    	$str_limit = '';
    
    	//查询条件
    	$str_search = ' WHERE';
    
    	//查询参数类型
    	$type_search = '';
    
    	//查询参数数组
    	$param_search = array();
    	
    	//查询条件：
    	if (!empty($startDate)){
    		$str_search .= ' AND bm.createTime >= ?';
    		$type_search .= 's';
    		$param_search[] = $startDate.' 00:00:00';
    	}
    
    	if (!empty($endDate)){
    		$str_search .= ' AND bm.createTime <= ?';
    		$type_search .= 's';
    		$param_search[] = $endDate.' 23:59:59';
    	}
    	
    	if (!empty($bill_no)){
    		$str_search .= ' AND bm.bill_no = ?';
    		$type_search .= 's';
    		$param_search[] = $bill_no;
    	}
    	
    	if (!empty($package_no)){
    		$str_search .= ' AND bm.package_no = ?';
    		$type_search .= 'i';
    		$param_search[] = $package_no;
    	}
    	
    	if (!empty($deliver_name)){
    		$str_search .= ' AND bm.deliver_name = ?';
    		$type_search .= 's';
    		$param_search[] = encryptD($deliver_name);
    	}
    	
    	if (!empty($deliver_mobile)){
    		$str_search .= ' AND bm.deliver_mobile = ?';
    		$type_search .= 's';
    		$param_search[] = encryptD($deliver_mobile);
    	}
    	
    	if (!empty($payTypeID)){
    		$str_search .= ' AND bm.payTypeID = ?';
    		$type_search .= 'i';
    		$param_search[] = $payTypeID;
    	}
    
    	//分页
    	if (!empty($page) && !empty($rp)){
    		$str_limit = sprintf(' LIMIT %d, %d', ($page - 1) * $rp, $rp);
    	}
    
    	//排序
    	if (!empty($sortname) && !empty($sortorder)){
    		$str_order = sprintf(' ORDER BY %s %s', $sortname, $sortorder);
    	}
    
    	//组装SQL语句
    	if (!empty($type_search)){
    		$str_search = str_replace('WHERE AND', 'WHERE', $str_search);
    		$sql_count = $sql_count.$str_search;
    		$sql_search = $sql_search.$str_search.$str_order.$str_limit;
    	}else {
    		$sql_search = $sql_search.$str_order.$str_limit;
    	}
    
    	$value_count = empty($type_search) ? $this->dbca->stmt_query($sql_count) : $this->dbca->stmt_query($sql_count,$type_search,$param_search);
    
    	$return_data = array();
    
    	if ($value_count){
    		//总记录条数
    		$importData['total'] = $value_count[0]->countNum;
    
    		//得到查询结果
    		$result_search = empty($type_search) ? $this->dbca->stmt_query($sql_search) : $this->dbca->stmt_query($sql_search,$type_search,$param_search);
    		
    		if ($result_search){
    			foreach ($result_search as $value){
    				$return_data[] = array(
    						'bill_no'				=> $value->bill_no,
    						'createTime'			=> $value->createTime,
    						'package_no'			=> $value->package_no,
    						'pay_type'				=> $value->pay_type,
    						'payTypeName'			=> $value->payTypeName,
    						'bill_totalMoney'		=> $value->bill_totalMoney,
    						'bill_totalPaid'		=> $value->bill_totalPaid,
    						'bill_subtract_money'	=> $value->bill_subtract_money,
    						'bill_coupon_money'		=> $value->bill_coupon_money,
    						'deliver_name'			=> decryptD($value->deliver_name),
    						'deliver_province'		=> $value->deliver_province,
    						'deliver_city'			=> $value->deliver_city,
    						'deliver_county'		=> $value->deliver_county,
    						'deliver_address'		=> decryptD($value->deliver_address),
    						'deliver_mobile'		=> decryptD($value->deliver_mobile),
    						'pay_status'			=> $value->pay_status,
    						'deliver_feePaid'		=> $value->deliver_feePaid,
    						'deduct_amount'			=> $value->deduct_amount
    				);
    			}
    		}
    	}else {
    		$importData['total'] = 0;
    		$return_data = array();
    	}
    
    	return $return_data;
	}
	
	//获取对账包裹信息
	public function searchCODBillInfo (&$importData=array()){
		$startDate = isset($importData['startDate']) ? trim($importData['startDate']) : '';
		$endDate = isset($importData['endDate']) ? trim($importData['endDate']) : '';
		$page = isset($importData['page']) ? trim($importData['page']) : '';//当前页
		$rp = isset($importData['rp']) ? trim($importData['rp']) : '';//每页记录数
		$sortname = isset($importData['sortname']) ? trim($importData['sortname']) : '';//排序字段名
		$sortorder = isset($importData['sortorder']) ? trim($importData['sortorder']) : '';//排序方式
		$bill_no = isset($importData['bill_no']) ? trim($importData['bill_no']) : '';
		$package_no = isset($importData['package_no']) ? trim($importData['package_no']) : '';
		$deliver_name = isset($importData['deliver_name']) ? trim($importData['deliver_name']) : '';
		$deliver_mobile = isset($importData['deliver_mobile']) ? trim($importData['deliver_mobile']) : '';
	
		$sql_count = "SELECT COUNT(DISTINCT wpp.package_no) countNum FROM wms_prepare_package wpp 
						INNER JOIN wms_package_bill wpb ON wpp.package_no = wpb.package_no
						INNER JOIN bill_master bm ON wpb.bill_no = bm.bill_no";
	
		$sql_search = "SELECT wpb.bill_no, bm.createTime, wpp.package_no, wpp.storeName, wpp.hdfk_paid, wpp.dispatch_time,
						wpp.logistics_name, wpp.goods_weight, wpp.deliver_name, wpp.deliver_mobile, wpp.deliver_province,
						wpp.deliver_city, wpp.deliver_county FROM (SELECT * FROM wms_prepare_package WHERESTR ORDERSTR LIMITSTR) wpp 
						INNER JOIN wms_package_bill wpb ON wpp.package_no = wpb.package_no
						INNER JOIN bill_master bm ON wpb.bill_no = bm.bill_no";
		 
		//排序
		$str_order = '';
	
		//分页
		$str_limit = '';
	
		//查询条件
		$str_search = ' WHERE';
		$whereStr = '';
		$conStr = '';
	
		//查询参数类型
		$type_search = '';
	
		//查询参数数组
		$param_search = array();
		 
		//查询条件：
		if (!empty($startDate)){
			$str_search .= ' AND wpp.dispatch_time >= ?';
			$whereStr .= ' AND dispatch_time >= ?';
			$type_search .= 's';
			$param_search[] = $startDate.' 00:00:00';
		}
	
		if (!empty($endDate)){
			$str_search .= ' AND wpp.dispatch_time <= ?';
			$whereStr .= ' AND dispatch_time <= ?';
			$type_search .= 's';
			$param_search[] = $endDate.' 23:59:59';
		}
		 
		if (!empty($bill_no)){
			$str_search .= ' AND bm.bill_no = ?';
			$conStr .= ' AND bm.bill_no = ?';
			$type_search .= 's';
			$param_search[] = $bill_no;
		}
		 
		if (!empty($package_no)){
			$str_search .= ' AND wpp.package_no = ?';
			$whereStr .= ' AND package_no = ?';
			$type_search .= 'i';
			$param_search[] = $package_no;
		}
		 
		if (!empty($deliver_name)){
			$str_search .= ' AND wpp.deliver_name = ?';
			$whereStr .= ' AND deliver_name = ?';
			$type_search .= 's';
			$param_search[] = encryptD($deliver_name);
		}
		 
		if (!empty($deliver_mobile)){
			$str_search .= ' AND wpp.deliver_mobile = ?';
			$whereStr .= ' AND deliver_mobile = ?';
			$type_search .= 's';
			$param_search[] = encryptD($deliver_mobile);
		}
		 
		//分页
		if (!empty($page) && !empty($rp)){
			$str_limit = sprintf(' LIMIT %d, %d', ($page - 1) * $rp, $rp);
		}
	
		//排序
		if (!empty($sortname) && !empty($sortorder)){
			$str_order = sprintf(' ORDER BY %s %s', $sortname, $sortorder);
		}
	
		$sql_search = str_replace('LIMITSTR', $str_limit, $sql_search);
		$sql_search = str_replace('ORDERSTR', $str_order, $sql_search);
		if (!empty($whereStr)){
			$whereStr = ' WHERE'.$whereStr;
		}
		$sql_search = str_replace('WHERESTR', $whereStr, $sql_search);
		if (!empty($conStr)){
			$sql_search = $sql_search.$conStr;
		}
		
		//组装SQL语句
		if (!empty($type_search)){
			$str_search = str_replace('WHERE AND', 'WHERE', $str_search);
			$sql_count = $sql_count.$str_search;
			$sql_search = str_replace('WHERE AND', 'WHERE', $sql_search);
		}
		
		$value_count = empty($type_search) ? $this->dbca->stmt_query($sql_count) : $this->dbca->stmt_query($sql_count,$type_search,$param_search);
	
		$return_data = array();
	
		if ($value_count){
			//总记录条数
			$importData['total'] = $value_count[0]->countNum;
	
			//得到查询结果
			$result_search = empty($type_search) ? $this->dbca->stmt_query($sql_search) : $this->dbca->stmt_query($sql_search,$type_search,$param_search);
			
			if ($result_search){
				$tempArr = array();
				$packageNoArr = array();
				foreach ($result_search as $value){
					if (!in_array($value->package_no, $packageNoArr)){
						$tempArr[$value->package_no] = array(
								'bill_no'				=> $value->bill_no,
								'createTime'			=> $value->createTime,
								'package_no'			=> $value->package_no,
								'storeName'				=> $value->storeName,
								'hdfk_paid'				=> $value->hdfk_paid,
								'dispatch_time'			=> $value->dispatch_time,
								'logistics_name'		=> $value->logistics_name,
								'goods_weight'			=> $value->goods_weight,
								'deliver_name'			=> decryptD($value->deliver_name),
								'deliver_province'		=> $value->deliver_province,
								'deliver_city'			=> $value->deliver_city,
								'deliver_county'		=> $value->deliver_county,
								'deliver_address'		=> decryptD($value->deliver_address),
								'deliver_mobile'		=> decryptD($value->deliver_mobile)
								);
						$packageNoArr[] = $value->package_no;
					}else {
						$tempArr[$value->package_no]['bill_no'] .= ','.$value->bill_no;
					}
				}
				
				if (!empty($tempArr)){
					foreach ($tempArr as $val){
						$return_data[] = array(
								'bill_no'				=> $val['bill_no'],
								'createTime'			=> $val['createTime'],
								'package_no'			=> $val['package_no'],
								'storeName'				=> $val['storeName'],
								'hdfk_paid'				=> $val['hdfk_paid'],
								'dispatch_time'			=> $val['dispatch_time'],
								'logistics_name'		=> $val['logistics_name'],
								'goods_weight'			=> $val['goods_weight'],
								'deliver_name'			=> $val['deliver_name'],
								'deliver_province'		=> $val['deliver_province'],
								'deliver_city'			=> $val['deliver_city'],
								'deliver_county'		=> $val['deliver_county'],
								'deliver_address'		=> $val['deliver_address'],
								'deliver_mobile'		=> $val['deliver_mobile']
						);
					}
				}
			}
		}else {
			$importData['total'] = 0;
			$return_data = array();
		}
	
		return $return_data;
	}
}